﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;

namespace ExportItem2Lua
{
    public partial class FrmMain : Form
    {
        public FrmMain()
        {
            InitializeComponent();
        }

        public List<string> GetExcelTableNames(string excelFileName)
        {
            List<string> tableName = new List<string>();
            if (File.Exists(excelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
                            "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
                {
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        tableName.Add(dt.Rows[i][2].ToString().Trim());
                    }
                }
            }
            return tableName;
        }

        private DataSet GetDataSetFromExcel(string fileName, string sheetName, bool firstRowIsHeader)
        {
            if (!System.IO.File.Exists(fileName))
                throw new ArgumentException("file is not exist!");

            var ds = new DataSet();
            var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=Excel 8.0;";
            //if (firstRowIsHeader)  
            //    strConn += "HDR=Yes;";  
            strConn = string.Format(strConn, fileName);
            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataTable excelSchema = null;
                string sql = "select * from [{0}]";
                var adapter = new OleDbDataAdapter();
                adapter.SelectCommand = conn.CreateCommand();

                if (string.IsNullOrEmpty(sheetName))
                {
                    excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                       new object[] { null, null, null, "TABLE" });
                    foreach (DataRow row in excelSchema.Rows)
                    {
                        var sheet = row["TABLE_NAME"].ToString();
                        sql = string.Format(sql, sheet);
                        adapter.SelectCommand.CommandText = sql;
                        adapter.Fill(ds, sheet);
                    }
                }
                else
                {
                    sql = string.Format(sql, sheetName);
                    adapter.SelectCommand.CommandText = sql;
                    adapter.Fill(ds, sheetName);
                }
                conn.Close();
            }
            return ds;
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {

        }

        private void Tool_Open_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Title = "Excel文件";
                ofd.FileName = "";
                //ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                ofd.Filter = "Excel文件(*.xls)|*.xls";
                ofd.ValidateNames = true;
                ofd.CheckPathExists = true;
                string strName = string.Empty;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    strName = ofd.FileName;
                }

                if (strName == "")
                {
                    cboTable.Items.Clear();
                    txtPath.Text = "";
                    MessageBox.Show("没有选择Excel文件！无法进行数据导入", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
                    txtPath.Text = strName;
                    List<string> tableName = GetExcelTableNames(strName);
                    foreach (string str in tableName)
                    {
                        cboTable.Items.Add(str);
                    }
                    this.Cursor = System.Windows.Forms.Cursors.Arrow;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void Tool_Import_Click(object sender, EventArgs e)
        {
            this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
            if (txtPath.Text != "" && cboTable.Text != "")
            {
                Grid.DataSource = GetDataSetFromExcel(txtPath.Text, cboTable.Text, true).Tables[0];
            }
            this.Cursor = System.Windows.Forms.Cursors.Arrow;
        }

        private void Tool_Export_Click(object sender, EventArgs e)
        {
            if (Grid.Rows.Count < 1)
            {
                MessageBox.Show("请先导入数据。", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            List<string> Columns = new List<string>();
            for (int i = 0; i < Grid.Columns.Count; i++)
            {
                Columns.Add(Grid.Columns[i].Name);
            }
            FrmOutput frm = new FrmOutput(Columns,Grid);
            frm.ShowDialog();
        }

        private void System_Exit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void Tool_Help_Click(object sender, EventArgs e)
        {
            string strMsg = "使用步骤：\r\n1.请选择数据源(暂时只支持.xls格式)\r\n\r\n2.选择表(下拉框中会列出所有表的名称)\r\n\r\n3.点击导入，会显示当前表中所有的数据，然后点击导出即可。";
            MessageBox.Show(strMsg,"帮助",MessageBoxButtons.OK,MessageBoxIcon.Information);
        }

        private void Tool_About_Click(object sender, EventArgs e)
        {
            string strMsg = "Lua脚本导出工具 V0.1\r\n\r\nXZGame 荣誉出品...";
            MessageBox.Show(strMsg, "关于", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void Tool_Helps_Click(object sender, EventArgs e)
        {
            string strMsg = "使用步骤：\r\n1.请选择数据源(暂时只支持.xls格式)\r\n\r\n2.选择表(下拉框中会列出所有表的名称)\r\n\r\n3.点击导入，会显示当前表中所有的数据，然后点击导出即可。";
            MessageBox.Show(strMsg, "帮助", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void Tool_Exits_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}
